package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.User;

public class UserDAO {
	public UserDAO() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/xiao?characterEncoding=UTF-8", "root", "12345");
	}

	public List<User> list() {
		return list(0, Short.MAX_VALUE);
	}

	public List<User> list(int start, int count) {
		List<User> users = new ArrayList<User>();

		String sql = "select * from user order by id limit ?,?";
		try {
			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, start);
			preparedStatement.setInt(2, count);

			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				User user = new User();
				int id = resultSet.getInt(1);
				String username = resultSet.getString(2);
				String password = resultSet.getString(3);
				user.id = id;
				user.username = username;
				user.password = password;
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return users;
	}

	public void add(User user) {
		String sql = "insert into user values(null,?,?)";
		try {
			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.username);
			preparedStatement.setString(2, user.password);
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete(int id) {
		try {
			Connection connection = getConnection();
			Statement statement = connection.createStatement();
			String sql = "delete from user where id = " + id;
			statement.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public User get(int id) {
		User user = null;
		try {
			Connection connection = getConnection();
			Statement statement = connection.createStatement();
			String sql = "select * from user where id = " + id;
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				user = new User();
				// Integer id = resultSet.getInt("id");
				String username = resultSet.getString("username");
				String password = resultSet.getString("password");
				user.id = id;
				user.username = username;
				user.password = password;

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;
	}

	public void upadte(User user) {
		try {
			String sql = "update user set username = ? ,password =? where id =? ";
			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,user.username);
			preparedStatement.setString(2,user.password);
			preparedStatement.setInt(3, user.id);
			preparedStatement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
